﻿namespace OleDbDAL.Sales
{
    using DBUtility;
    using IDal.Sales;
    using Model.Sales;
    using Model.Systems;
    using System;
    using System.Data;
    using System.Text;
    using WinFormSession;

    public class OrdersCommApp : IOrdersComm
    {
        private string dbname = "Systems";

        public void Add(OrdersCommEnt model)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("insert into T_OrdersComm(");
            builder.Append("OrdersId,LotNum,SupplierId,DlId,CommId,CommName,Amount,AmoutUnit,StandardMoney,AmoutMoney,Discount,Rate,RateAllowance,IsReturn,ReturnAmount,Cost,IsGiveGuestPresent,Remark");
            builder.Append(")");
            builder.Append(" values (");
            builder.Append("" + model.OrdersId + ",");
            builder.Append("'" + model.LotNum + "',");
            builder.Append("'" + model.SupplierId + "',");
            builder.Append("" + model.DlId + ",");
            builder.Append("" + model.CommId + ",");
            builder.Append("'" + model.CommName + "',");
            builder.Append("" + model.Amount + ",");
            builder.Append("" + model.AmoutUnit + ",");
            builder.Append("" + model.StandardMoney + ",");
            builder.Append("" + model.AmoutMoney + ",");
            builder.Append("" + model.Discount + ",");
            builder.Append("" + model.Rate + ",");
            builder.Append("" + model.RateAllowance + ",");
            builder.Append("" + model.IsReturn + ",");
            builder.Append("" + model.ReturnAmount + ",");
            builder.Append("" + model.Cost + ",");
            builder.Append("" + model.IsGiveGuestPresent + ",");
            builder.Append("'" + model.Remark + "'");
            builder.Append(")");
            DbHelperOledb.ExecuteSql(this.dbname, builder.ToString());
        }

        public void Delete(int SaleCommId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("delete from T_OrdersComm ");
            builder.Append(" where SaleCommId=" + SaleCommId);
            DbHelperOledb.ExecuteSql(this.dbname, builder.ToString());
        }

        public DataTable GetList(string strWhere)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select * from T_OrdersComm ");
            if (strWhere.Trim() != "")
            {
                builder.Append(" where " + strWhere);
            }
            builder.Append(" order by SaleCommId ");
            return DbHelperOledb.Query(this.dbname, builder.ToString());
        }

        public int GetMaxId(string swhere)
        {
            return DbHelperOledb.GetMaxID(this.dbname, "SaleCommId", "T_OrdersComm", swhere);
        }

        public OrdersCommEnt GetModel(int SaleCommId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select SaleCommId,OrdersId,LotNum,SupplierId,DlId,CommId,CommName,Amount,AmoutUnit,StandardMoney,AmoutMoney,Discount,Rate,RateAllowance,IsReturn,ReturnAmount,Cost,IsGiveGuestPresent,Remark from T_OrdersComm ");
            builder.Append(" where SaleCommId=" + SaleCommId);
            OrdersCommEnt ent = new OrdersCommEnt();
            DataTable table = DbHelperOledb.Query(this.dbname, builder.ToString());
            ent.SaleCommId = SaleCommId;
            if (table.Rows.Count > 0)
            {
                if (table.Rows[0]["OrdersId"].ToString() != "")
                {
                    ent.OrdersId = int.Parse(table.Rows[0]["OrdersId"].ToString());
                }
                ent.LotNum = table.Rows[0]["LotNum"].ToString();
                ent.SupplierId = table.Rows[0]["SupplierId"].ToString();
                if (table.Rows[0]["DlId"].ToString() != "")
                {
                    ent.DlId = int.Parse(table.Rows[0]["DlId"].ToString());
                }
                if (table.Rows[0]["CommId"].ToString() != "")
                {
                    ent.CommId = int.Parse(table.Rows[0]["CommId"].ToString());
                }
                ent.CommName = table.Rows[0]["CommName"].ToString();
                if (table.Rows[0]["Amount"].ToString() != "")
                {
                    ent.Amount = decimal.Parse(table.Rows[0]["Amount"].ToString());
                }
                if (table.Rows[0]["AmoutUnit"].ToString() != "")
                {
                    ent.AmoutUnit = int.Parse(table.Rows[0]["AmoutUnit"].ToString());
                }
                if (table.Rows[0]["StandardMoney"].ToString() != "")
                {
                    ent.StandardMoney = decimal.Parse(table.Rows[0]["StandardMoney"].ToString());
                }
                if (table.Rows[0]["AmoutMoney"].ToString() != "")
                {
                    ent.AmoutMoney = decimal.Parse(table.Rows[0]["AmoutMoney"].ToString());
                }
                if (table.Rows[0]["Discount"].ToString() != "")
                {
                    ent.Discount = decimal.Parse(table.Rows[0]["Discount"].ToString());
                }
                if (table.Rows[0]["Rate"].ToString() != "")
                {
                    ent.Rate = decimal.Parse(table.Rows[0]["Rate"].ToString());
                }
                if (table.Rows[0]["RateAllowance"].ToString() != "")
                {
                    ent.RateAllowance = decimal.Parse(table.Rows[0]["RateAllowance"].ToString());
                }
                if (table.Rows[0]["IsReturn"].ToString() != "")
                {
                    ent.IsReturn = int.Parse(table.Rows[0]["IsReturn"].ToString());
                }
                if (table.Rows[0]["ReturnAmount"].ToString() != "")
                {
                    ent.ReturnAmount = decimal.Parse(table.Rows[0]["ReturnAmount"].ToString());
                }
                if (table.Rows[0]["Cost"].ToString() != "")
                {
                    ent.Cost = decimal.Parse(table.Rows[0]["Cost"].ToString());
                }
                if (table.Rows[0]["IsGiveGuestPresent"].ToString() != "")
                {
                    ent.IsGiveGuestPresent = int.Parse(table.Rows[0]["IsGiveGuestPresent"].ToString());
                }
                ent.Remark = table.Rows[0]["Remark"].ToString();
                return ent;
            }
            return null;
        }

        public DataTable GetPageList(int PageSize, int PageIndex, string strWhere)
        {
            string orderString = "";
            int pageCount = 0;
            int recordCount = 0;
            StringBuilder builder = new StringBuilder();
            builder.Append("select * from T_OrdersComm ");
            return DbHelperOledb.ExecutePager(this.dbname, PageIndex, PageSize, "", builder.ToString(), strWhere, orderString, out pageCount, out recordCount, "SaleCommId");
        }

        public int GetSearchDataCount(string strWhere)
        {
            StringBuilder builder = new StringBuilder();
            UserEnt ent = (UserEnt) WinformSession.GetSession()["user"];
            builder.Append("select count(*)");
            builder.Append(" from T_OrdersComm,T_Orders ");
            builder.Append("where T_OrdersComm.OrdersId=T_orders.OrdersId and T_Orders.IsBlackOut=0 and ChainNum=" + ent.ChainNum);
            builder.Append(strWhere);
            DataTable table = DbHelperOledb.Query(this.dbname, builder.ToString());
            if (table.Rows.Count > 0)
            {
                return int.Parse(table.Rows[0][0].ToString());
            }
            return 0;
        }

        public DataTable SearchData(string strWhere)
        {
            StringBuilder builder = new StringBuilder();
            UserEnt ent = (UserEnt) WinformSession.GetSession()["user"];
            builder.Append("select SaleCommId,OrderNum,LotNum,CreateTime,");
            builder.Append("(select DlName from T_Digitallibrary where T_Digitallibrary.DlId=T_OrdersComm.DlId) as DlName");
            builder.Append(",CommName,Amount,");
            builder.Append("(select parName from T_Parameter where T_OrdersComm.AmoutUnit=T_Parameter.ParId) as AmoutUnit,");
            builder.Append("StandardMoney,AmoutMoney,Discount,Cost,GuestName from T_OrdersComm,T_Orders ");
            builder.Append("where T_OrdersComm.IsGiveGuestPresent<>1 and T_Orders.IsBlackOut=0 and T_OrdersComm.OrdersId=T_orders.OrdersId and ChainNum=" + ent.ChainNum);
            builder.Append(strWhere);
            builder.Append(" order by SaleCommId desc");
            return DbHelperOledb.Query(this.dbname, builder.ToString());
        }

        public DataTable SearchData(int PageSize, int PageIndex, string strWhere)
        {
            string orderString = "SaleCommId desc";
            int pageCount = 0;
            int recordCount = 0;
            StringBuilder builder = new StringBuilder();
            UserEnt ent = (UserEnt) WinformSession.GetSession()["user"];
            builder.Append("select SaleCommId,OrderNum,LotNum,CreateTime,T_OrdersComm.DlId");
            builder.Append(",CommName,Amount,T_OrdersComm.AmoutUnit,");
            builder.Append("StandardMoney,AmoutMoney,Discount,Cost,GuestName from T_OrdersComm,T_Orders ");
            builder.Append("where T_OrdersComm.IsGiveGuestPresent<>1 and T_Orders.IsBlackOut=0 and T_OrdersComm.OrdersId=T_orders.OrdersId and ChainNum=" + ent.ChainNum);
            builder.Append(strWhere);
            return DbHelperOledb.ExecutePager(this.dbname, PageIndex, PageSize, "", builder.ToString(), "", orderString, out pageCount, out recordCount, "SaleCommId");
        }

        public void Update(OrdersCommEnt model)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("update T_OrdersComm set ");
            builder.Append("OrdersId=" + model.OrdersId + ",");
            builder.Append("LotNum='" + model.LotNum + "',");
            builder.Append("SupplierId='" + model.SupplierId + "',");
            builder.Append("DlId=" + model.DlId + ",");
            builder.Append("CommId=" + model.CommId + ",");
            builder.Append("CommName='" + model.CommName + "',");
            builder.Append("Amount=" + model.Amount + ",");
            builder.Append("AmoutUnit=" + model.AmoutUnit + ",");
            builder.Append("StandardMoney=" + model.StandardMoney + ",");
            builder.Append("AmoutMoney=" + model.AmoutMoney + ",");
            builder.Append("Discount=" + model.Discount + ",");
            builder.Append("Rate=" + model.Rate + ",");
            builder.Append("RateAllowance=" + model.RateAllowance + ",");
            builder.Append("IsReturn=" + model.IsReturn + ",");
            builder.Append("ReturnAmount=" + model.ReturnAmount + ",");
            builder.Append("Cost=" + model.Cost + ",");
            builder.Append("IsGiveGuestPresent=" + model.IsGiveGuestPresent + ",");
            builder.Append("Remark='" + model.Remark + "'");
            builder.Append(" where SaleCommId=" + model.SaleCommId + "");
            DbHelperOledb.ExecuteSql(this.dbname, builder.ToString());
        }
    }
}

